package org.example.sbdemo.dao;

import org.example.sbdemo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserDao extends JpaRepository<User, Integer> {
    // Spring Data JPA会根据方法名自动生成查询语句
    List<User> findByGender(String gender);

    // 原生查询：查找最小缺失的正整数 id（填补空洞）
    @Query(value = "SELECT (t.id + 1) AS missing FROM users t LEFT JOIN users t2 ON t.id + 1 = t2.id WHERE t2.id IS NULL ORDER BY t.id LIMIT 1", nativeQuery = true)
    Integer findSmallestMissingId();

    @Query(value = "SELECT COALESCE(MAX(id), 0) FROM users", nativeQuery = true)
    Integer findMaxId();
}
